


# ======================================================================
#                              维度表数据
# ======================================================================



# todo: ==================== 特殊(不变化) ====================

# ======================================================================
#                       省份表：base_province
# ======================================================================
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://node101:3306/gmall \
--username root \
--password 123456 \
--target-dir /origin_data/gmall/db/base_province/2023-09-01 \
--delete-target-dir \
--query "SELECT
  id,
  name,
  region_id,
  area_code,
  iso_code,
  iso_3166_2
FROM base_province
WHERE \$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec gzip \
--null-string '\\N' \
--null-non-string '\\N'


# ======================================================================
#                       区域表：base_region
# ======================================================================
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://node101:3306/gmall \
--username root \
--password 123456 \
--target-dir /origin_data/gmall/db/base_region/2023-09-01 \
--delete-target-dir \
--query "SELECT
  id,
  region_name
FROM base_region
WHERE \$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec gzip \
--null-string '\\N' \
--null-non-string '\\N'


# ======================================================================
#                       优惠劵表：coupon_info
# ======================================================================
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://node101:3306/gmall \
--username root \
--password 123456 \
--target-dir /origin_data/gmall/db/coupon_info/2023-09-01 \
--delete-target-dir \
--query "SELECT
id,
coupon_name,
coupon_type,
condition_amount,
condition_num,
activity_id,
benefit_amount,
benefit_discount,
create_time,
range_type,
limit_num,
taken_count,
start_time,
end_time,
operate_time,
expire_time
FROM coupon_info
WHERE \$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec gzip \
--null-string '\\N' \
--null-non-string '\\N'


# ======================================================================
#                       活动表：activity_info
# ======================================================================
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://node101:3306/gmall \
--username root \
--password 123456 \
--target-dir /origin_data/gmall/db/activity_info/2023-09-01 \
--delete-target-dir \
--query "SELECT
id,
activity_name,
activity_type,
start_time,
end_time,
create_time
from activity_info
WHERE \$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec gzip \
--null-string '\\N' \
--null-non-string '\\N'


# ======================================================================
#                       商品表：sku_info
# ======================================================================
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://node101:3306/gmall \
--username root \
--password 123456 \
--target-dir /origin_data/gmall/db/sku_info/2023-09-01 \
--delete-target-dir \
--query "SELECT
id,
spu_id,
price,
sku_name,
sku_desc,
weight,
tm_id,
category3_id,
is_sale,
create_time
from sku_info
WHERE \$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec gzip \
--null-string '\\N' \
--null-non-string '\\N'



# ======================================================================
#                       用户表：user_info
# ======================================================================
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://node101:3306/gmall \
--username root \
--password 123456 \
--target-dir /origin_data/gmall/db/user_info/2023-09-06 \
--delete-target-dir \
--query "SELECT
id,
login_name,
nick_name,
name,
phone_num,
email,
user_level,
birthday,
gender,
create_time,
operate_time
from user_info
WHERE substring(create_time,1,10) ='2023-09-06' and \$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec gzip \
--null-string '\\N' \
--null-non-string '\\N'



# ======================================================================
#                       一级分类表：base_category1
# ======================================================================
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://node101:3306/gmall \
--username root \
--password 123456 \
--target-dir /origin_data/gmall/db/base_category1/2023-09-01 \
--delete-target-dir \
--query "SELECT
id,
name
from base_category1
WHERE \$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec gzip \
--null-string '\\N' \
--null-non-string '\\N'

# ======================================================================
#                       二级分类表：base_category2
# ======================================================================
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://node101:3306/gmall \
--username root \
--password 123456 \
--target-dir /origin_data/gmall/db/base_category2/2023-09-01 \
--delete-target-dir \
--query "SELECT
id,
name,
category1_id
from base_category2
WHERE \$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec gzip \
--null-string '\\N' \
--null-non-string '\\N'




# ======================================================================
#                       三级分类表：base_category3
# ======================================================================
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://node101:3306/gmall \
--username root \
--password 123456 \
--target-dir /origin_data/gmall/db/base_category3/2023-09-01 \
--delete-target-dir \
--query "SELECT
id,
name,
category2_id
from base_category3
WHERE \$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec gzip \
--null-string '\\N' \
--null-non-string '\\N'



# ======================================================================
#                       字典表：base_dic
# ======================================================================
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://node101:3306/gmall \
--username root \
--password 123456 \
--target-dir /origin_data/gmall/db/base_dic/2023-09-01 \
--delete-target-dir \
--query "SELECT
dic_code,
dic_name,
parent_code,
create_time,
operate_time
from base_dic
WHERE \$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec gzip \
--null-string '\\N' \
--null-non-string '\\N'


# ======================================================================
#                              事实表数据
# ======================================================================


# ======================================================================
#                       收藏表：favor_info
# ======================================================================
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://node101:3306/gmall \
--username root \
--password 123456 \
--target-dir /origin_data/gmall/db/favor_info/2023-09-06 \
--delete-target-dir \
--query "SELECT
id,
user_id,
sku_id,
spu_id,
is_cancel,
create_time,
cancel_time
from favor_info
WHERE substring(create_time,1,10) ='2023-09-06' and \$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec gzip \
--null-string '\\N' \
--null-non-string '\\N'




# ======================================================================
#                      加购表：cart_info
# ======================================================================
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://node101:3306/gmall \
--username root \
--password 123456 \
--target-dir /origin_data/gmall/db/cart_info/2023-09-06 \
--delete-target-dir \
--query "SELECT
id,
user_id,
sku_id,
cart_price,
sku_num,
sku_name,
create_time,
operate_time,
is_ordered,
order_time,
source_type,
source_id
from cart_info
WHERE substring(create_time,1,10) ='2023-09-06' and \$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec gzip \
--null-string '\\N' \
--null-non-string '\\N'




# ======================================================================
#                        下单表：order_info
# ======================================================================
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://node101:3306/gmall \
--username root \
--password 123456 \
--target-dir /origin_data/gmall/db/order_info/2023-09-06 \
--delete-target-dir \
--query "SELECT
 id,
total_amount,
order_status,
user_id,
payment_way,
delivery_address,
out_trade_no,
create_time,
operate_time,
expire_time,
tracking_no,
province_id,
activity_reduce_amount,
coupon_reduce_amount,
original_total_amount,
feight_fee,
feight_fee_reduce
from order_info
WHERE substring(create_time,1,10) ='2023-09-06' and \$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec gzip \
--null-string '\\N' \
--null-non-string '\\N'




# ======================================================================
#                      下单表：order_detail
# ======================================================================
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://node101:3306/gmall \
--username root \
--password 123456 \
--target-dir /origin_data/gmall/db/order_detail/2023-09-06 \
--delete-target-dir \
--query "SELECT
  id,
order_id,
sku_id,
sku_name,
order_price,
sku_num,
create_time,
source_type,
source_id,
split_total_amount,
split_activity_amount,
split_coupon_amount
from order_detail
WHERE substring(create_time,1,10) ='2023-09-06' and \$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec gzip \
--null-string '\\N' \
--null-non-string '\\N'





# ======================================================================
#                      支付表：payment_info
# ======================================================================
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://node101:3306/gmall \
--username root \
--password 123456 \
--target-dir /origin_data/gmall/db/payment_info/2023-09-06 \
--delete-target-dir \
--query "SELECT
id,
out_trade_no,
order_id,
user_id,
payment_type,
trade_no,
total_amount,
subject,
payment_status,
create_time,
callback_time
from payment_info
WHERE substring(create_time,1,10) ='2023-09-06' and \$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec gzip \
--null-string '\\N' \
--null-non-string '\\N'





# ======================================================================
#                      退款表：refund_payment
# ======================================================================
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://node101:3306/gmall \
--username root \
--password 123456 \
--target-dir /origin_data/gmall/db/refund_payment/2023-09-06 \
--delete-target-dir \
--query "SELECT
id,
out_trade_no,
order_id,
sku_id,
payment_type,
trade_no,
total_amount,
subject,
refund_status,
create_time,
callback_time
from refund_payment
WHERE substring(create_time,1,10) ='2023-09-06' and \$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec gzip \
--null-string '\\N' \
--null-non-string '\\N'

# ======================================================================
#                      评论表表：comment_info
# ======================================================================
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://node101:3306/gmall \
--username root \
--password 123456 \
--target-dir /origin_data/gmall/db/comment_info/2023-09-06 \
--delete-target-dir \
--query "SELECT
 id,
user_id,
sku_id,
spu_id,
order_id,
appraise,
create_time
from comment_info
WHERE substring(create_time,1,10) ='2023-09-06' and \$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec gzip \
--null-string '\\N' \
--null-non-string '\\N'


#新增10个表

#todo: 全量
品牌表 base_trademark
优惠规则表 activity_rule
SKU平台属性表 sku_attr_value
SPU商品表 spu_info
SKU销售属性表 sku_sale_attr_value
#todo:增量
退单表(特殊) order_refund_info
订单状态表 order_status_log
订单明细活动关联表 order_detail_activity
订单明细优惠劵关联表 order_detail_coupon
#todo:新增和变化
优惠劵领用表 coupon_use

# ======================================================================
#                     品牌表 base_trademark
# ======================================================================
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://node101:3306/gmall \
--username root \
--password 123456 \
--target-dir /origin_data/gmall/db/base_trademark/2023-09-01 \
--delete-target-dir \
--query "SELECT
  id,
  tm_name
FROM base_trademark
WHERE \$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec gzip \
--null-string '\\N' \
--null-non-string '\\N'



# ======================================================================
#                   优惠规则表 activity_rule
# ======================================================================
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://node101:3306/gmall \
--username root \
--password 123456 \
--target-dir /origin_data/gmall/db/activity_rule/2023-09-01 \
--delete-target-dir \
--query "select
           id,
           activity_id,
           activity_type,
           condition_amount,
           condition_num,
           benefit_amount,
           benefit_discount,
           benefit_level
      from activity_rule
WHERE \$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec gzip \
--null-string '\\N' \
--null-non-string '\\N'


# ======================================================================
#                  SKU平台属性表 sku_attr_value
# ======================================================================
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://node101:3306/gmall \
--username root \
--password 123456 \
--target-dir /origin_data/gmall/db/sku_attr_value/2023-09-01 \
--delete-target-dir \
--query "select
            id,
            attr_id,
            value_id,
            sku_id,
            attr_name,
            value_name
          from sku_attr_value
WHERE \$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec gzip \
--null-string '\\N' \
--null-non-string '\\N'

# ======================================================================
#                     SPU商品表 spu_info
# ======================================================================
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://node101:3306/gmall \
--username root \
--password 123456 \
--target-dir /origin_data/gmall/db/spu_info/2023-09-01 \
--delete-target-dir \
--query "select
          id,
          spu_name,
          category3_id,
          tm_id
         from spu_info
WHERE \$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec gzip \
--null-string '\\N' \
--null-non-string '\\N'


# ======================================================================
#                     SKU销售属性表 sku_sale_attr_value
# ======================================================================
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://node101:3306/gmall \
--username root \
--password 123456 \
--target-dir /origin_data/gmall/db/sku_sale_attr_value/2023-09-01 \
--delete-target-dir \
--query "select
           id,
           sku_id,
           spu_id,
           sale_attr_value_id,
           sale_attr_id,
           sale_attr_name,
           sale_attr_value_name
        from sku_sale_attr_value
WHERE \$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec gzip \
--null-string '\\N' \
--null-non-string '\\N'

# ======================================================================
#                     退单表(特殊) order_refund_info
# ======================================================================
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://node101:3306/gmall \
--username root \
--password 123456 \
--target-dir /origin_data/gmall/db/order_refund_info/2023-09-01 \
--delete-target-dir \
--query "select
          id,
          user_id,
          order_id,
          sku_id,
          refund_type,
          refund_num,
          refund_amount,
          refund_reason_type,
          refund_status,
          create_time
       from order_refund_info
WHERE substring(create_time,1,10) = '2023-09-01' and
\$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec gzip \
--null-string '\\N' \
--null-non-string '\\N'

# ======================================================================
#                     订单状态表 order_status_log
# ======================================================================
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://node101:3306/gmall \
--username root \
--password 123456 \
--target-dir /origin_data/gmall/db/order_status_log/2023-09-01 \
--delete-target-dir \
--query "select
            id,
            order_id,
            order_status,
            operate_time
          from order_status_log
WHERE substring(operate_time,1,10) = '2023-09-01' and
\$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec gzip \
--null-string '\\N' \
--null-non-string '\\N'

# ======================================================================
#                    订单明细活动关联表 order_detail_activity
# ======================================================================
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://node101:3306/gmall \
--username root \
--password 123456 \
--target-dir /origin_data/gmall/db/order_detail_activity/2023-09-01 \
--delete-target-dir \
--query "select
           id,
           order_id,
           order_detail_id,
           activity_id,
           activity_rule_id,
           sku_id,
           create_time
           from
          order_detail_activity
WHERE substring(create_time,1,10) = '2023-09-01' and
\$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec gzip \
--null-string '\\N' \
--null-non-string '\\N'



# ======================================================================
#                    订单明细优惠劵关联表 order_detail_coupon
# ======================================================================
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://node101:3306/gmall \
--username root \
--password 123456 \
--target-dir /origin_data/gmall/db/order_detail_coupon/2023-09-01 \
--delete-target-dir \
--query "select
id,
order_id,
order_detail_id,
coupon_id,
coupon_use_id,
sku_id,
create_time
from order_detail_coupon
WHERE substring(create_time,1,10) = '2023-09-01' and
\$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec gzip \
--null-string '\\N' \
--null-non-string '\\N'


# ======================================================================
#                    优惠劵领用表 coupon_use
# ======================================================================
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://node101:3306/gmall \
--username root \
--password 123456 \
--target-dir /origin_data/gmall/db/coupon_use/2023-09-01 \
--delete-target-dir \
--query "select
          id,
          coupon_id,
          user_id,
          order_id,
          coupon_status,
          get_time,
          using_time,
          used_time,
          expire_time
        from coupon_use
WHERE substring(get_time,1,10) = '2023-09-01' and
\$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec gzip \
--null-string '\\N' \
--null-non-string '\\N'







